package org.ttitfly.workflow.engine.persistence;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;

import org.ttitfly.workflow.engine.persistence.jdbc.JdbcException;
import org.ttitfly.workflow.engine.persistence.jdbc.JdbcTemplate;
import org.ttitfly.workflow.engine.persistence.jdbc.RowMapper;
import org.ttitfly.workflow.engine.run.ProcessInstance;
import org.ttitfly.workflow.engine.run.Task;


/**
 * 持久化服务基本功能实现
 * 
 * @author 潘海春
 * @version 2009-9-22
 */
public class BasicPersistenceService implements PersistenceService {

	/** 创建流程实例表的sql */
	private static final String PROCESS_INSTANCE_SCHEMA = "CREATE TABLE WF_PROCESS_INSTANCE (ID INTEGER IDENTITY, PARENT_ID INTEGER, PROCESS_NAME VARCHAR(128), PROCESS_VERSION FLOAT, NAME VARCHAR(128), STATUS INTEGER, ACTIVATION_TIME	TIMESTAMP, CLOSE_TIME TIMESTAMP, CREATOR VARCHAR(128))";

	/** 创建任务表的sql */
	private static final String TASK_SCHEMA = "CREATE TABLE WF_TASK (ID INTEGER IDENTITY, PARENT_ID INTEGER, UNIT_NAME VARCHAR(128), STATUS INTEGER, ACTIVATION_TIME TIMESTAMP, CLOSE_TIME TIMESTAMP, ASSIGNERS VARCHAR(4096), OPERATOR VARCHAR(128), ACTION VARCHAR(128), FOREIGN KEY (PARENT_ID) REFERENCES WF_PROCESS_INSTANCE (ID))";

	/** 创建流程实例表外键的sql */
	private static final String CONSTRAINT_SCHEMA = "ALTER TABLE WF_PROCESS_INSTANCE ADD CONSTRAINT FK_PARENT_ID FOREIGN KEY (PARENT_ID) REFERENCES WF_TASK (ID)";

	/** 查询流程实例sql语句 */
	private static final String PROCESS_INSTANCE_QUERY_SQL = "SELECT * FROM WF_PROCESS_INSTANCE WHERE ID = (SELECT PARENT_ID FROM T_WF_TASK WHERE ID = ?)";

	/** 保存流程实例sql语句 */
	private static final String PROCESS_INSTANCE_SAVE_SQL = "INSERT INTO WF_PROCESS_INSTANCE (PROCESS_NAME, PROCESS_VERSION, PARENT_ID, NAME, STATUS, ACTIVATION_TIME, CREATOR) VALUES (?, ?, ?, ?, ?, ?, ?)";

	/** 更新流程实例sql语句 */
	private static final String PROCESS_INSTANCE_UPDATE_SQL = "UPDATE WF_PROCESS_INSTANCE SET STATUS = ?, CLOSE_TIME = ? WHERE ID = ?";

	/** 查询任务sql语句 */
	private static final String TASK_QUERY_SQL = "SELECT * FROM WF_TASK WHERE PARENT_ID = ?";

	/** 保存任务sql语句 */
	private static final String TASK_SAVE_SQL = "INSERT INTO WF_TASK (PARENT_ID, UNIT_NAME, STATUS, ACTIVATION_TIME, ASSIGNERS) VALUES (?, ?, ?, ?, ?)";

	/** 更新任务sql语句 */
	private static final String TASK_UPDATE_SQL = "UPDATE WF_TASK SET STATUS = ?, CLOSE_TIME = ?, OPERATOR = ?, ACTION = ? WHERE ID = ?";

	/**
	 * 流程实例行映射
	 */
	private RowMapper<ProcessInstance> processInstanceMapper = new RowMapper<ProcessInstance>() {
		@Override
		public ProcessInstance mapRow(ResultSet resultSet) throws SQLException {
			ProcessInstance processInstance = new ProcessInstance();
			processInstance.setId(resultSet.getInt("ID"));
			Integer parentId = resultSet.getInt("PARENT_ID");
			if (parentId != null && parentId > 0) {
				processInstance.setParent(getTask(parentId));
			}
			processInstance.setProcessName(resultSet.getString("PROCESS_NAME"));
			processInstance.setProcessVersion(resultSet.getFloat("PROCESS_VERSION"));
			processInstance.setName(resultSet.getString("NAME"));
			processInstance.setStatus(resultSet.getInt("STATUS"));
			processInstance.setActivationTime(resultSet.getTimestamp("ACTIVATION_TIME"));
			processInstance.setCloseTime(resultSet.getTimestamp("CLOSE_TIME"));
			processInstance.setCreator(resultSet.getString("CREATOR"));
			return processInstance;
		}
	};

	/**
	 * 任务行映射
	 */
	private RowMapper<Task> taskMapper = new RowMapper<Task>() {
		@Override
		public Task mapRow(ResultSet resultSet) throws SQLException {
			Task task = new Task();
			task.setId(resultSet.getInt("ID"));
			task.setUnitName(resultSet.getString("UNIT_NAME"));
			task.setStatus(resultSet.getInt("STATUS"));
			task.setActivationTime(resultSet.getTimestamp("ACTIVATION_TIME"));
			task.setCloseTime(resultSet.getTimestamp("CLOSE_TIME"));
			task.setAssigners(resultSet.getString("ASSIGNERS"));
			task.setOperator(resultSet.getString("OPERATOR"));
			task.setAction(resultSet.getString("ACTION"));
			return task;
		}
	};

	/**
	 * jdbc template
	 */
	private JdbcTemplate jdbcTemplate;

	/**
	 * 构造函数
	 * 
	 * @param dataSource
	 */
	public BasicPersistenceService(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		// 初始化schema
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet rs = meta.getTables(null, null, null, new String[] { "TABLE" });
			boolean tableExists = false;
			while (rs.next()) {
				if (rs.getString("TABLE_NAME").equals("WF_PROCESS_INSTANCE")) {
					tableExists = true;
					break;
				}
			}
			if (!tableExists) {
				Statement stmt = connection.createStatement();
				stmt.addBatch(PROCESS_INSTANCE_SCHEMA);
				stmt.addBatch(TASK_SCHEMA);
				stmt.addBatch(CONSTRAINT_SCHEMA);
				stmt.executeBatch();
				stmt.close();
			}
		} catch (Exception e) {
			throw new JdbcException(e);
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new JdbcException(e);
			}
		}
	}

	@Override
	public Task getTask(int id) {
		ProcessInstance parent = (ProcessInstance) jdbcTemplate.queryForObject(PROCESS_INSTANCE_QUERY_SQL, processInstanceMapper, id);
		List<Task> tasks = (List<Task>) jdbcTemplate.queryForList(TASK_QUERY_SQL, taskMapper, parent.getId());
		Task result = null;
		for (Task task : tasks) {
			task.setProcessInstance(parent);
			parent.getTasks().add(task);
			if (task.getId() == id) {
				result = task;
			}
		}
		return result;
	}

	@Override
	public void save(ProcessInstance processInstance) {
		int id = (Integer) jdbcTemplate.executeInsert(PROCESS_INSTANCE_SAVE_SQL, 
				processInstance.getProcessName(),
				processInstance.getProcessVersion(), 
				(processInstance.getParent() == null ? null : processInstance.getParent().getId()), 
				processInstance.getName(),
				processInstance.getStatus(), 
				processInstance.getActivationTime(), 
				processInstance.getCreator());
		processInstance.setId(id);
	}

	@Override
	public void save(Task task) {
		int id = (Integer) jdbcTemplate.executeInsert(TASK_SAVE_SQL, 
				task.getProcessInstance().getId(), 
				task.getUnitName(), 
				task.getStatus(), 
				task.getActivationTime(),
				task.getAssigners());
		task.setId(id);
	}

	@Override
	public void update(ProcessInstance processInstance) {
		jdbcTemplate.executeUpdate(PROCESS_INSTANCE_UPDATE_SQL,
				processInstance.getStatus(), 
				processInstance.getCloseTime(),
				processInstance.getId());
	}

	@Override
	public void update(Task task) {
		jdbcTemplate.executeUpdate(TASK_UPDATE_SQL, 
				task.getStatus(),
				task.getCloseTime(), 
				task.getOperator(), 
				task.getAction(),
				task.getId());
	}

	@Override
	public int countTask(String principal, String processName,
			String activityName, int processInstanceId, int[] status) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<Task> findTask(String principal, String processName,
			String activityName, int processInstanceId, int[] status,
			int start, int count) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void deleteProcessInstance(int id) throws PersistenceException {
		// TODO Auto-generated method stub
		
	}

}
